/**
 * 高性能批量处理器
 * 用于优化大量数据的数据库操作
 */

class BatchProcessor {
  constructor(dbPool, options = {}) {
    this.dbPool = dbPool;
    this.batchSize = options.batchSize || 1000; // 每批处理1000条
    this.maxConcurrency = options.maxConcurrency || 3; // 最大并发批次
    this.retryAttempts = options.retryAttempts || 3; // 重试次数
    this.retryDelay = options.retryDelay || 1000; // 重试延迟(ms)
    
    console.log('🚀 批量处理器已初始化', {
      batchSize: this.batchSize,
      maxConcurrency: this.maxConcurrency,
      retryAttempts: this.retryAttempts
    });
  }

  /**
   * 批量更新记录
   */
  async batchUpdate(records, updateFields, progressCallback) {
    const totalRecords = records.length;
    const batches = this.createBatches(records, this.batchSize);
    let processedCount = 0;
    let successCount = 0;
    let errorCount = 0;
    
    console.log(`📦 开始批量更新: ${totalRecords} 条记录，分为 ${batches.length} 批`);
    
    // 并发处理批次
    const semaphore = new Semaphore(this.maxConcurrency);
    const promises = batches.map(async (batch, batchIndex) => {
      return semaphore.acquire(async () => {
        try {
          const result = await this.processBatchWithRetry(batch, updateFields, batchIndex);
          processedCount += batch.length;
          successCount += result.success;
          errorCount += result.errors;
          
          // 更新进度
          if (progressCallback) {
            progressCallback({
              processed: processedCount,
              total: totalRecords,
              percentage: Math.round((processedCount / totalRecords) * 100),
              currentBatch: batchIndex + 1,
              totalBatches: batches.length,
              success: successCount,
              errors: errorCount
            });
          }
          
          return result;
        } catch (error) {
          console.error(`❌ 批次 ${batchIndex + 1} 处理失败:`, error.message);
          errorCount += batch.length;
          throw error;
        }
      });
    });
    
    try {
      await Promise.all(promises);
      console.log(`✅ 批量更新完成: 成功 ${successCount}，失败 ${errorCount}`);
      return { success: successCount, errors: errorCount, total: totalRecords };
    } catch (error) {
      console.error('❌ 批量更新失败:', error.message);
      throw error;
    }
  }

  /**
   * 创建批次
   */
  createBatches(records, batchSize) {
    const batches = [];
    for (let i = 0; i < records.length; i += batchSize) {
      batches.push(records.slice(i, i + batchSize));
    }
    return batches;
  }

  /**
   * 带重试的批次处理
   */
  async processBatchWithRetry(batch, updateFields, batchIndex) {
    let lastError;
    
    for (let attempt = 1; attempt <= this.retryAttempts; attempt++) {
      try {
        return await this.processBatch(batch, updateFields, batchIndex);
      } catch (error) {
        lastError = error;
        console.warn(`⚠️ 批次 ${batchIndex + 1} 第 ${attempt} 次尝试失败:`, error.message);
        
        if (attempt < this.retryAttempts) {
          await this.delay(this.retryDelay * attempt); // 指数退避
        }
      }
    }
    
    throw lastError;
  }

  /**
   * 处理单个批次
   */
  async processBatch(batch, updateFields, batchIndex) {
    const startTime = Date.now();
    
    try {
      // 开始事务
      await this.dbPool.query('BEGIN TRANSACTION');
      
      let successCount = 0;
      let errorCount = 0;
      
      // 🔧 修复：直接逐条执行更新，避免批量SQL语法问题
      for (const record of batch) {
        try {
          const { sql, params } = this.buildUpdateSQL(record, updateFields);
          await this.dbPool.query(sql, params);
          successCount++;
        } catch (error) {
          console.warn(`⚠️ 记录 ${record.id} 更新失败:`, error.message);
          errorCount++;
        }
      }
      
      // 提交事务
      await this.dbPool.query('COMMIT');
      
      const duration = Date.now() - startTime;
      console.log(`✅ 批次 ${batchIndex + 1} 完成: ${successCount}/${batch.length} 条记录，耗时 ${duration}ms`);
      
      return { success: successCount, errors: errorCount };
      
    } catch (error) {
      // 回滚事务
      try {
        await this.dbPool.query('ROLLBACK');
      } catch (rollbackError) {
        console.error('❌ 事务回滚失败:', rollbackError.message);
      }
      
      throw error;
    }
  }

  /**
   * 构建更新SQL
   */
  buildUpdateSQL(record, updateFields) {
    const fields = [];
    const params = [];

    // 🔧 修复：updateFields是按记录ID组织的对象
    const recordFields = updateFields[record.id] || {};

    for (const [field, value] of Object.entries(recordFields)) {
      if (value !== undefined && value !== null) {
        fields.push(`${field} = ?`);
        params.push(value);
      }
    }

    if (fields.length === 0) {
      throw new Error('没有可更新的字段');
    }

    // 添加更新时间
    fields.push('updated_at = datetime("now")');

    const sql = `UPDATE pph_records SET ${fields.join(', ')} WHERE id = ?`;
    params.push(record.id);

    return { sql, params };
  }

  /**
   * 延迟函数
   */
  delay(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
  }

  /**
   * 获取处理统计
   */
  getStats() {
    return {
      batchSize: this.batchSize,
      maxConcurrency: this.maxConcurrency,
      retryAttempts: this.retryAttempts
    };
  }
}

/**
 * 信号量实现（控制并发）
 */
class Semaphore {
  constructor(maxConcurrency) {
    this.maxConcurrency = maxConcurrency;
    this.currentConcurrency = 0;
    this.queue = [];
  }

  async acquire(task) {
    return new Promise((resolve, reject) => {
      this.queue.push({ task, resolve, reject });
      this.tryNext();
    });
  }

  tryNext() {
    if (this.currentConcurrency >= this.maxConcurrency || this.queue.length === 0) {
      return;
    }

    this.currentConcurrency++;
    const { task, resolve, reject } = this.queue.shift();

    task()
      .then(resolve)
      .catch(reject)
      .finally(() => {
        this.currentConcurrency--;
        this.tryNext();
      });
  }
}

/**
 * 高性能重算处理器
 */
class RecomputeProcessor extends BatchProcessor {
  constructor(dbPool, targetCache, averageCache, options = {}) {
    super(dbPool, options);
    this.targetCache = targetCache;
    this.averageCache = averageCache;
  }

  /**
   * 高性能重算记录
   */
  async recomputeRecords(records, options = {}, progressCallback) {
    const { recalcPph = false } = options;
    
    console.log(`🔄 开始高性能重算: ${records.length} 条记录`);
    
    // 预加载所有需要的目标值和预估值
    await this.preloadTargetData(records);
    
    // 批量处理
    const updateFields = await this.prepareUpdateFields(records, recalcPph);
    
    return await this.batchUpdate(records, updateFields, progressCallback);
  }

  /**
   * 预加载目标数据到缓存
   */
  async preloadTargetData(records) {
    const uniqueTargets = new Set();
    
    for (const record of records) {
      const key = `${record.material_code}_${record.line_name}_${record.is_whole_machine}_${record.record_date}`;
      uniqueTargets.add(key);
    }
    
    console.log(`📥 预加载 ${uniqueTargets.size} 个唯一目标值`);
    
    // 批量查询缺失的目标值
    const missingTargets = [];
    for (const key of uniqueTargets) {
      const [materialCode, lineName, isWholeMachine, recordDate] = key.split('_');
      if (!this.targetCache.get(materialCode, lineName, isWholeMachine === '1', recordDate)) {
        missingTargets.push({ materialCode, lineName, isWholeMachine: isWholeMachine === '1', recordDate });
      }
    }
    
    if (missingTargets.length > 0) {
      console.log(`🔍 查询 ${missingTargets.length} 个缺失的目标值`);
      await this.batchLoadTargets(missingTargets);
    }
  }

  /**
   * 批量加载目标值
   */
  async batchLoadTargets(targets) {
    // 🔧 修复：使用单独查询避免UNION ALL语法问题
    const targetResults = [];
    const estimateResults = [];

    for (const t of targets) {
      try {
        // 🔧 修复：查询目标值（使用正确的字段名）
        const targetQuery = `
          SELECT
            CASE
              WHEN ? = 1 THEN whole_machine_target
              ELSE motor_target
            END as target_pph,
            record_date as target_record_date,
            takt_time as target_takt_time,
            motor_takt_time as target_motor_takt_time
          FROM pph_targets
          WHERE material_code = ? AND line_name = ?
                AND record_date <= ?
                AND is_active = 1
          ORDER BY record_date DESC LIMIT 1
        `;

        const targetResult = await this.dbPool.query(targetQuery, [
          t.isWholeMachine ? 1 : 0, t.materialCode, t.lineName, t.recordDate
        ]);

        if (targetResult && targetResult[0]) {
          const rawTargetPph = targetResult[0].target_pph;

          // 改进目标值处理：只有有效的正数才被认为是有效目标值
          let finalTargetPph = null;
          if (rawTargetPph !== null && rawTargetPph !== undefined && rawTargetPph !== '') {
            const numValue = Number(rawTargetPph);
            if (!isNaN(numValue) && numValue > 0) {
              finalTargetPph = numValue;
            }
          }

          // 记录结果（即使目标值为null也要记录，用于调试）
          targetResults.push({
            material_code: t.materialCode,
            line_name: t.lineName,
            is_whole_machine: t.isWholeMachine ? 1 : 0,
            record_date: t.recordDate,
            target_pph: finalTargetPph,
            target_record_date: targetResult[0].target_record_date,
            target_takt_time: targetResult[0].target_takt_time != null ? Number(targetResult[0].target_takt_time) : null,
            target_motor_takt_time: targetResult[0].target_motor_takt_time != null ? Number(targetResult[0].target_motor_takt_time) : null
          });

          // 如果目标值为null，记录警告信息
          if (finalTargetPph === null) {
            console.warn(`⚠️ 物料 ${t.materialCode} 在 ${t.lineName} 线的目标值为空 (生产类型: ${t.isWholeMachine ? '整机' : '整机+电机'})`);
          }
        } else {
          console.warn(`⚠️ 未找到物料 ${t.materialCode} 在 ${t.lineName} 线的目标值记录`);
        }

        // 🔧 修复：恢复原始正确的预估值查询逻辑
        const estimateQuery = `
          SELECT estimate_pph
          FROM pph_estimates
          WHERE UPPER(material_code) = UPPER(?)
            AND TRIM(line_name) = TRIM(?)
          ORDER BY updated_at DESC
          LIMIT 1
        `;

        const estimateResult = await this.dbPool.query(estimateQuery, [
          t.materialCode, t.lineName
        ]);

        if (estimateResult && estimateResult[0] && estimateResult[0].estimate_pph) {
          estimateResults.push({
            material_code: t.materialCode,
            line_name: t.lineName,
            is_whole_machine: t.isWholeMachine ? 1 : 0,
            record_date: t.recordDate,
            estimate_pph: estimateResult[0].estimate_pph
          });
        }

      } catch (error) {
        console.warn(`⚠️ 查询目标值失败: ${t.materialCode}@${t.lineName}`, error.message);
      }
    }
    
    // 🔧 修复：将结果存入缓存
    for (const result of targetResults || []) {
      this.targetCache.set(
        result.material_code,
        result.line_name,
        result.is_whole_machine === 1,
        result.record_date,
        {
          target_pph: result.target_pph,
          target_record_date: result.target_record_date,
          target_takt_time: result.target_takt_time,
          target_motor_takt_time: result.target_motor_takt_time,
          estimate_pph: null
        }
      );
    }

    for (const result of estimateResults || []) {
      const existing = this.targetCache.get(
        result.material_code,
        result.line_name,
        result.is_whole_machine === 1,
        result.record_date
      ) || {};

      existing.estimate_pph = result.estimate_pph;

      this.targetCache.set(
        result.material_code,
        result.line_name,
        result.is_whole_machine === 1,
        result.record_date,
        existing
      );
    }

    console.log(`✅ 批量加载完成: 目标值 ${targetResults?.length || 0}，预估值 ${estimateResults?.length || 0}`);
  }

  /**
   * 准备更新字段
   */
  async prepareUpdateFields(records, recalcPph) {
    const updateFields = {};
    
    for (const record of records) {
      const recordFields = {};
      
      // 从缓存获取目标值和预估值
      const targetData = this.targetCache.get(
        record.material_code,
        record.line_name,
        record.is_whole_machine === 1,
        record.record_date
      );
      
      if (targetData) {
        recordFields.target_pph = targetData.target_pph;
        recordFields.target_record_date = targetData.target_record_date;
        recordFields.estimate_pph = targetData.estimate_pph;
        
        // 计算效率达成率
        if (record.calculated_pph && targetData.target_pph) {
          recordFields.efficiency_rate = Math.round((record.calculated_pph / targetData.target_pph * 100) * 100) / 100;
        }
      }
      
      // 计算时间相关字段
      const timeFields = this.calculateTimeFields(record);
      Object.assign(recordFields, timeFields);
      
      // 可选：重算PPH
      if (recalcPph) {
        const newPph = this.recalculatePPH(record);
        if (newPph !== null) {
          recordFields.calculated_pph = newPph;
        }
      }
      
      // 计算平均PPH
      const averagePph = await this.calculateAveragePPH(record);
      if (averagePph !== null) {
        recordFields.average_pph = averagePph;
      }
      
      updateFields[record.id] = recordFields;
    }
    
    return updateFields;
  }

  /**
   * 计算时间相关字段
   */
  calculateTimeFields(record) {
    const fields = {};
    
    try {
      const startTime = record.start_time;
      const endTime = record.end_time;
      const breakMinutes = Number(record.break_minutes || 0);
      const qty = Number(record.quantity || 0);
      
      if (startTime && endTime) {
        const start = new Date(`1970-01-01T${startTime}`);
        const end = new Date(`1970-01-01T${endTime}`);
        let totalMinutes = (end - start) / (1000 * 60);
        
        if (totalMinutes < 0) totalMinutes += 24 * 60; // 跨天处理
        
        const netMinutes = Math.max(0, totalMinutes - breakMinutes);
        fields.net_working_minutes = Math.round(netMinutes * 100) / 100;
        
        // 计算实际节拍时间
        if (qty > 0 && netMinutes > 0) {
          fields.actual_takt_seconds = Math.round((netMinutes / qty) * 60 * 100) / 100;
        }
      }
    } catch (error) {
      console.warn(`⚠️ 记录 ${record.id} 时间计算失败:`, error.message);
    }
    
    return fields;
  }

  /**
   * 重新计算PPH
   */
  recalculatePPH(record) {
    try {
      const qty = Number(record.quantity || 0);
      const people = Number(record.people_count || 0);
      const netMinutes = Number(record.net_working_minutes || 0);
      
      if (qty > 0 && people > 0 && netMinutes > 0) {
        const netHours = netMinutes / 60;
        return Math.round((qty / (netHours * people)) * 10) / 10;
      }
    } catch (error) {
      console.warn(`⚠️ 记录 ${record.id} PPH重算失败:`, error.message);
    }
    
    return null;
  }

  /**
   * 计算平均PPH（恢复原始正确逻辑）
   * 公式: PPH平均值 = SUM(完工数量) ÷ (AVG(人数) × SUM(净工作时间) / 60)
   * 分组: 同一日期、同一生产线、同一物料编码
   */
  async calculateAveragePPH(record) {
    const cacheKey = `${record.record_date}_group`;
    const cached = this.averageCache.get(record.material_code, record.line_name, cacheKey);

    if (cached !== null) {
      return cached;
    }

    try {
      // 🔧 修复：恢复原始正确的分组计算逻辑
      const avgQuery = `
        SELECT
          SUM(CASE WHEN quantity IS NOT NULL THEN quantity ELSE 0 END) as total_quantity,
          SUM(CASE WHEN net_working_minutes IS NOT NULL THEN net_working_minutes ELSE 0 END) as total_minutes,
          AVG(CASE WHEN total_people IS NOT NULL THEN total_people ELSE 0 END) as avg_people,
          COUNT(*) as record_count
        FROM pph_records
        WHERE record_date = ? AND line_name = ? AND material_code = ?
      `;

      const result = await this.dbPool.query(avgQuery, [
        record.record_date,
        record.line_name,
        record.material_code
      ]);

      if (result && result[0] && result[0].total_minutes > 0 && result[0].avg_people > 0) {
        // PPH平均值 = SUM(完工数量) ÷ (AVG(人数) × SUM(净工作时间) / 60)
        const avgPph = result[0].total_quantity / (result[0].total_minutes / 60 * result[0].avg_people);
        const roundedAvgPph = Math.round(avgPph * 100) / 100;

        this.averageCache.set(record.material_code, record.line_name, cacheKey, roundedAvgPph);

        console.log(`📊 计算平均PPH [${record.record_date}|${record.line_name}|${record.material_code}]: ${result[0].total_quantity}件 ÷ (${result[0].avg_people}人 × ${result[0].total_minutes/60}小时) = ${roundedAvgPph}`);

        return roundedAvgPph;
      }
    } catch (error) {
      console.warn(`⚠️ 记录 ${record.id} 平均PPH计算失败:`, error.message);
    }

    return null;
  }
}

module.exports = { BatchProcessor, RecomputeProcessor, Semaphore };
